import shutil
from configparser import ConfigParser
import openpyxl
import win32com.client
from openpyxl.utils import get_column_letter

from tools_pmc import (fill_blue, fill_yellow, fill_orange,
                       border_gray,
                       font_ERP,
                       today_str1, lj_tyl1, lj_tyl2, lj_tyl, lj_config, lj_1, lj_zyl,
                       table_array_wlxxb
                       )
from tool_zyl import zyl

def vlookup(lookup_value, table_array):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return [row[5].value, row[6].value, row[7].value, row[8].value, row[13].value, row[14].value]


excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
excel.DisplayAlerts = False
e_wb_tywlpdb_1 = excel.Workbooks.Open(lj_tyl1)
e_ws_tywlpdb_1 = e_wb_tywlpdb_1.Worksheets(1)
e_wb_tywlpdb_2 = excel.Workbooks.Open(lj_tyl2)
e_ws_tywlpdb_2 = e_wb_tywlpdb_2.Worksheets(1)

data = e_ws_tywlpdb_2.Range("A2:AG" + str(e_ws_tywlpdb_2.UsedRange.Rows.Count)).Copy()
e_ws_tywlpdb_1.Paste(Destination=e_ws_tywlpdb_1.Range("A" + str(e_ws_tywlpdb_1.UsedRange.Rows.Count + 1)))

e_ws_tywlpdb_1.Sort.SortFields.Add(Key=e_ws_tywlpdb_1.Range("A2"),
                                   Order=win32com.client.constants.xlAscending)  # 添加排序条件，按顺序添加
e_ws_tywlpdb_1.Sort.SetRange(
    e_ws_tywlpdb_1.Range("A2:AG" + str(e_ws_tywlpdb_1.UsedRange.Rows.Count)))  # 这里需要注意，设置需要排序的范围。
e_ws_tywlpdb_1.Sort.Apply()  # 执行排序

e_wb_tywlpdb_1.SaveAs(lj_tyl)
e_wb_tywlpdb_1.Close()
e_wb_tywlpdb_2.Close()

wb = openpyxl.load_workbook(lj_tyl)
ws_tyl = wb['Sheet1']
ws_tyl.insert_cols(17, 2)

for i in range(1, ws_tyl.max_row + 1):
    if i > 1:
        ws_tyl.cell(i, 1).number_format = "0"
        ws_tyl.cell(i, 4).number_format = "0"
        ws_tyl.cell(i, 1).data_type = "int"
        ws_tyl.cell(i, 4).data_type = "int"
    list_temp = vlookup(ws_tyl.cell(i, 1).value, table_array_wlxxb)
    ws_tyl.cell(i, 11).value = list_temp[0]
    ws_tyl.cell(i, 12).value = list_temp[1]
    ws_tyl.cell(i, 13).value = list_temp[2]
    ws_tyl.cell(i, 14).value = list_temp[3]
    ws_tyl.cell(i, 17).value = list_temp[4]
    ws_tyl.cell(i, 18).value = list_temp[5]
    ws_tyl.cell(i, 17).border = border_gray
    ws_tyl.cell(i, 18).border = border_gray
    ws_tyl.cell(i, 17).font = font_ERP
    ws_tyl.cell(i, 18).font = font_ERP

    # noinspection PyTypeChecker
    ws_tyl.row_dimensions[i].height = 16
list1 = ([14, 28, 21, 14, 28, 21, 10, 10, 10, 14, 14]
         + [8 for i in range(25)])
for i in range(1, ws_tyl.max_column + 1):
    ws_tyl.column_dimensions[get_column_letter(i)].width = list1[i - 1]

for i in range(1, 4):
    ws_tyl.cell(1, i).fill = fill_blue
    ws_tyl.cell(1, i + 3).fill = fill_orange
ws_tyl.cell(1, 12).fill = fill_yellow
ws_tyl.cell(1, 17).fill = fill_yellow
ws_tyl.cell(1, 18).fill = fill_yellow
ws_tyl.title = '通用物料判定表' + today_str1
ws_tyl.views.sheetView[0].zoomScale = 115
range_active = "A1:AI" + str(ws_tyl.max_row)
ws_tyl.auto_filter.ref = range_active
ws_tyl.freeze_panes = 'G2'  # 冻结窗口

wb.save(lj_tyl)

zyl()

e_wb_1 = excel.Workbooks.Open(lj_tyl)
e_ws_1 = e_wb_1.Worksheets(1)
e_wb_2 = excel.Workbooks.Open(lj_zyl)
for i in range(3, 0, -1):
    e_ws_2 = e_wb_2.Worksheets(i)
    e_ws_2.Copy(After=e_ws_1)
e_ws_1.Activate()
e_wb_1.SaveAs(lj_tyl)
e_wb_1.Close()
e_wb_2.Close()

cfg = ConfigParser()
cfg.read(lj_config)
if not cfg.get('set', 'Test mode'):
    shutil.copy(lj_tyl, lj_1)
print(today_str1 + ":tyl and zyl succeed!")
